package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.AppFunction;
import com.pagepub.expo.entity.AppRole;
@Repository
public class AppFunctionDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	
	private static String tableName="App_FUNCTION";
	
	
	private static String QUERY_ALL = "SELECT functionId,funKey,funName,parentId,functionLayer,functionOrder FROM "+tableName ;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"functionId,funKey,funName,parentId,functionLayer,functionOrder)VALUES(" +
			":functionId,:funKey,:funName,:parentId,:functionLayer,:functionOrder)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE functionId=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName + " SET " +
			"funKey=:funKey,funName=:funName,functionLayer=:functionLayer,functionOrder=:functionOrder,parentId=:parentId "+
			" WHERE functionId=:functionId";

	private final ParameterizedRowMapper<AppFunction> mapper = new ParameterizedRowMapper<AppFunction>() {
		public AppFunction mapRow(ResultSet rs, int rowNum) throws SQLException {
			AppFunction obj = new AppFunction();
			obj.setFunctionId(rs.getLong("functionId"));
			obj.setFunKey(rs.getString("funKey"));
			obj.setFunName(rs.getString("funName"));
			obj.setParentId(rs.getLong("parentId"));
			obj.setFunctionLayer(rs.getLong("functionLayer"));
			obj.setFunctionOrder(rs.getLong("functionOrder"));
			return obj;
		}
	};

	public List<AppFunction> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public AppFunction findById(Long id) {
		List<AppFunction> list = template.query(QUERY_ALL + " WHERE functionId=?",
				new Object[] { id }, mapper);
		if (list.size() == 0) {
			return null;
		} else
			return list.get(0);
	}
	
	
	public List<AppFunction>  findByParentId(Long id) {
		return template.query(QUERY_ALL + " WHERE parentId=?",	new Object[] { id }, mapper);
		
	}
	
	public List<AppFunction> findByRoleId(List<AppRole> roles) {
		String sql = "select t1.functionId,funKey,funName,parentId,functionLayer,functionOrder " +
				"from App_FUNCTION t1 , app_role_fun t2 where t1.functionId = t2.functionId and t1.functionLayer=1 and t2.roleid in(";
		for(int i=0; i<roles.size();i++){
			Long id = roles.get(i).getRoleId();
			if(i==0){
				sql+=id;
			}else{
				sql+=" ,"+id;
			}
		}
		sql+=") order by t1.functionOrder desc ";
		System.out.println(sql);
		List<AppFunction> parentList = template.query(sql , mapper);
		for(int j=0;j<parentList.size();j++){
			parentList.get(j).setSublist(findByParentId(parentList.get(j).getFunctionId()));
		}
		return parentList;
	}
	
	
	
	public void insert(AppFunction bean) {
		if(bean.getFunctionId()==null){
			bean.setFunctionId(new Long(appSeqDao.getSequence("app_function_seq")));
		}			
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(AppFunction bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, mapper);
		return lists;
		
	}
}
